﻿package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import utils.JDBCUtil;
/**
 * 该实现类运行需要MySql数据库test和表student
 * create table student(
	id int not null primary key auto_increment,
	name varchar(20) not null
	);
	
 * @author zhrb
 *
 */
public class StudentDaoJDBCImpl implements StudentDao {

	@Override
	public boolean writeStudent(Student student) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public Student readStudent(String name) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Student> getAllStudents() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int add(Student stu) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pst=null;
		String sql="insert into students(name) values(?)";
		int result=1;
		try {
			conn=JDBCUtil.getConnection();
			pst=conn.prepareStatement(sql);
			pst.setString(1, stu.getName());
			result=pst.executeUpdate();
			if(result<0){
				result=-1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.realeaseAll(null, pst, conn);
		}
		return result;
	}

	@Override
	public int delete(int sid) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pst=null;
		String sql="delete from students where id=?";
		int result=1;
		try {
			conn=JDBCUtil.getConnection();
			pst=conn.prepareStatement(sql);
			pst.setInt(1, sid);
			result=pst.executeUpdate();
			if(result<0){
				result=-1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.realeaseAll(null, pst, conn);
		}
		return result;
	}

	@Override
	public int update(Student stu) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pst=null;
		String sql="update students set name=? where id=?";
		int result=1;
		try {
			conn=JDBCUtil.getConnection();
			pst=conn.prepareStatement(sql);
			pst.setString(1, stu.getName());
			pst.setInt(2, stu.getId());
			result=pst.executeUpdate();
			if(result<0){
				result=-1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.realeaseAll(null, pst, conn);
		}
		return result;
	}

	@Override
	public List<Student> findAll() {
		// TODO Auto-generated method stub
		List<Student> stulist=new ArrayList<Student>();
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		String sql="select * from students";
		int result=1;
		try {
			conn=JDBCUtil.getConnection();
			pst=conn.prepareStatement(sql);
			rs=pst.executeQuery();
			while(rs.next()){
				stulist.add(new Student(rs.getInt("id"),rs.getString("name")));
			}
			if(result<0){
				result=-1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.realeaseAll(rs, pst, conn);
		}
		return stulist;
	}

	@Override
	public Student findById(int sid) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		String sql="select * from students where id=?";
		int result=1;
		Student student=null;
		try {
			conn=JDBCUtil.getConnection();
			pst=conn.prepareStatement(sql);
			pst.setInt(1, sid);
			rs=pst.executeQuery();
			while(rs.next()){
				student=new Student(rs.getInt("id"),rs.getString("name"));
			}
			if(result<0){
				result=-1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.realeaseAll(rs, pst, conn);
		}
		return student;
	}

	@Override
	public List<Student> findByName(String name) {
		// TODO Auto-generated method stub
		List<Student> stulist=new ArrayList<Student>();
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		//String sql="select * from students where name like ?";
		String sql="select * from where name like \"%\"?\"%\" ";
		int result=1;
		try {
			conn=JDBCUtil.getConnection();
			pst=conn.prepareStatement(sql);
		//	pst.setString(1, "%"+name+"%");
			pst.setString(1, name);
			rs=pst.executeQuery();
			while(rs.next()){
				stulist.add(new Student(rs.getInt("id"),rs.getString("name")));
			}
			if(result<0){
				result=-1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.realeaseAll(null, pst, conn);
		}
		return stulist;
	}

	@Override
	public void diplayAllStudent() {
		// TODO Auto-generated method stub
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		String sql = "select * from student";//表中有id和name这列
		try {
			conn = JDBCUtil.getConnection();
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				System.out.format("该学生的id=%d,姓名=%s\n",id,name);
			}
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.realeaseAll(rs,stat, conn);
		}
	}
}
